import os
import mysql.connector
from datetime import datetime

PATH = r'D:\03_画师下载new'


# painter=(id,painter_id,painter_name,last_download_time,pic_count,is_ai)

# index: 总页数
def get_painter():
    root_dirs = os.listdir(PATH)
    for root_dir in root_dirs:
        painter_name_list = root_dir.split('_')[:-1]
        painter_name = '_'.join(painter_name_list)
        painter_id = root_dir.split('_')[-1]
        pics = os.listdir(PATH + '\\' + root_dir)
        pic_count = len(pics)
        is_ai = 'F'
        # 获取最后下载时间
        last_download_pic = sorted(pics, reverse=True)[0]
        get_time = os.path.getatime(PATH + '\\' + root_dir + '\\' + last_download_pic)
        date_object = datetime.fromtimestamp(get_time)
        last_download_time = date_object.strftime('%Y-%m-%d %H:%M:%S')

        painter = (painter_id, painter_id, painter_name, last_download_time, pic_count, is_ai)
        if not query_painter_by_id(painter_id):
            insert_painter(painter)
        else:
            print('{}：已在数据库'.format(painter_id))


def query_painter_by_id(painter_id):
    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        passwd="1234",
        database="ry-vue"
    )
    mycursor = mydb.cursor()
    sql = "SELECT * FROM pixiv_painter WHERE id ='{}'".format(painter_id)
    mycursor.execute(sql)
    result = mycursor.fetchall()
    if len(result) == 1:
        return True
    if len(result) == 0:
        return False


def insert_painter(painter):
    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        passwd="1234",
        database="ry-vue"
    )
    mycursor = mydb.cursor()
    sql = "INSERT INTO pixiv_painter (id,painter_id,painter_name,last_download_time,pic_count,is_ai) VALUES (%s, %s, %s, %s, %s, %s)"
    mycursor.execute(sql, painter)
    mydb.commit()
    print(painter, '已插入')


if __name__ == '__main__':
    get_painter()
